﻿-- @Version passed in from caller
DECLARE @publicationId UNIQUEIDENTIFIER
DECLARE @ReturnSQL NVARCHAR(MAX)
SET @ReturnSQL = ''

DECLARE @t TABLE ( TableId uniqueidentifier, TableName nvarchar(100) )

DECLARE PublicationCur CURSOR FOR
SELECT PublicationId FROM Publication 
WHERE Version <= @Version
ORDER BY Version ASC

OPEN PublicationCur 

FETCH NEXT FROM PublicationCur INTO @publicationId
BEGIN
	INSERT INTO @t ( TableId, TableName )
	SELECT TableId, TableName FROM PublishedTable WHERE publicationId = @PublicationId
END

WHILE ( @@FETCH_STATUS = 0 )
BEGIN
	FETCH NEXT FROM PublicationCur INTO @publicationId
	IF ( @publicationId IS NOT NULL )
	BEGIN
		--delete the repbulished tables in the next publication
		DELETE @t WHERE TableName IN ( SELECT TableName FROM PublishedTable WHERE publicationid = @publicationId )

		--add the additional tables from this publication ( removing the one that are not supposed to be published )
		INSERT @t ( TableId, TableName ) 
		SELECT TableId, TableName FROM PublishedTable WHERE publicationid = @publicationId AND Operation = 1
	END
END

CLOSE PublicationCur
DEALLOCATE PublicationCur

SELECT @publicationId = PublicationId FROM Publication WHERE Version = @Version

DECLARE @ArticleSQL NVARCHAR(MAX)
SET @ArticleSQL = ''

DECLARE @TableId uniqueidentifier, @TableName nvarchar(255) 

DECLARE ArticleCur CURSOR FOR
SELECT TableId, TableName FROM @t 
ORDER BY TableName ASC

OPEN ArticleCur

FETCH NEXT FROM ArticleCur INTO @TableId, @TableName
WHILE ( @@FETCH_STATUS = 0 )
	BEGIN
	SET @ArticleSQL = dbo.[fn_db_GetSQLForArticle] ( @publicationId, @TableId )
	IF ( @ArticleSQL = '' )
		BEGIN
--		RAISERROR ( 'Invalid article in publication for table [%s]', 16, 1, @TableName)
		SELECT ''
		END
	SELECT @ReturnSQL = @ReturnSQL + @ArticleSQL 
	FETCH NEXT FROM ArticleCur INTO @TableId, @TableName
	END

CLOSE ArticleCur
DEALLOCATE ArticleCur

SELECT @ReturnSQL 
